TickerDefinitionExt

V8 Message Definiton

TickerDefinitionExt (external) records exist for all SpiderRock tickers including equity tickers (stocks and ETFs) as well as index tickers and synthetic tickers for future chains and option multihedge baskets.

METADATA

Attribute	Value
Topic	4335-product-definition
MLink Token	EquityDefinition
Product	SRAnalytics
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
ticker_at	enum - AssetType	PRI	'None'
ticker_ts	enum - TickerSrc	PRI	'None'
ticker_tk	VARCHAR(12)	PRI	''
symbolType	enum - SymbolType		'None'
name	VARCHAR(72)		''	Symbol name
issuerName	VARCHAR(72)		''	Name of issuer
cntryOfIncorp	VARCHAR(2)		''	ISO Issuer Country Code
parValue	FLOAT		0	Security Parvalue
parValueCurrency	VARCHAR(3)		''	Security Parvalue currency
pointValue	FLOAT		0
pointCurrency	enum - Currency		'None'
primaryExch	enum - PrimaryExchange		'None'
altID	INT		0	Alt Security ID number
mic	VARCHAR(4)		''	ISO Market Identification Code
micSeg	VARCHAR(4)		''	ISO Market Indentification Segment Code
symbol	VARCHAR(12)		''	stock symbol
issueClass	VARCHAR(1)		''	issue class of stock symbol if no issue class field will be blank
securityID	INT		0	Security ID number from the source Vendor SR Feed
sic	VARCHAR(4)		''	SIC Standard Industrial Classification code
cik	VARCHAR(10)		''	Central Index Key US specific
gics	VARCHAR(8)		''	Global Industry Classification Standard
lei	VARCHAR(20)		''	Legal Entity Identifier
naics	VARCHAR(6)		''	North American Industry Classification System
cfi	VARCHAR(6)		''	ISO Classification of Financial Instruments
cic	VARCHAR(4)		''	Complementay Identification Code
fisn	VARCHAR(40)		''	Financial Instrument Short Name
isin	VARCHAR(12)		''	ISIN code
bbgCompositeTicker	VARCHAR(12)		''	Bloomberg Composite Ticker
bbgExchangeTicker	VARCHAR(28)		''	Bloomberg Exchange Ticker
bbgCompositeGlobalID	VARCHAR(12)		''	Bloomberg Composite Global ID
bbgGlobalID	VARCHAR(12)		''	Bloomberg Global ID
bbgCurrency	VARCHAR(3)		''	Bloomberg Trading Currency
stkPriceInc	enum - StkPriceInc		'None'	Price increment None FullPenny Nickle
stkVolume	FLOAT		0	daily stock volume
futVolume	FLOAT		0	daily future volume
optVolume	FLOAT		0	daily option volume
exchString	VARCHAR(8)		''	exchanges listing any options on this underlying
hasOptions	enum - YesNo		'None'	Has Options flag
numOptions	INT		0	total number of listed options
sharesOutstanding	BIGINT		0	symbol shares outstanding represented in thousands actualsharesoutstanding sharesoutstanding 1000
otcPrimaryMarket	enum - OTCPrimaryMarket		'None'
otcTier	enum - OTCTier		'None'
otcReportingStatus	VARCHAR(1)		''
otcDisclosureStatus	INT		0
otcFlags	INT		0
tkDefSource	enum - TkDefSource		'None'	Ticker definition source None Vendor OTC SR Exchange
statusFlag	enum - TkStatusFlag		'None'
timeMetric	enum - TimeMetric		'None'	trading time metric 252 or 365 trading days or a weekly cycle type
tradingPeriod	enum - TradingPeriod		'None'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
ticker_tk	1
ticker_at	2
ticker_ts	3

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgTickerDefinitionExt` (
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `symbolType` ENUM('None','Equity','ADR','ETF','CashIndex','MutualFund','ShortETF','Future','Bond','DepReceipts','PreferredSec','PreferenceShare','StructuredProd','StapledSec','TradeableRights','Unit','Warrant','WhenIssued','ForeignIssue') NOT NULL DEFAULT 'None',
    `name` VARCHAR(72) NOT NULL DEFAULT '' COMMENT 'Symbol name',
    `issuerName` VARCHAR(72) NOT NULL DEFAULT '' COMMENT 'Name of issuer',
    `cntryOfIncorp` VARCHAR(2) NOT NULL DEFAULT '' COMMENT 'ISO Issuer Country Code',
    `parValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'Security Parvalue',
    `parValueCurrency` VARCHAR(3) NOT NULL DEFAULT '' COMMENT 'Security Parvalue currency',
    `pointValue` FLOAT NOT NULL DEFAULT 0,
    `pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `primaryExch` ENUM('None','NYSE','NYSEArca','NYSEMkt','NASDAQ','NASDAQBOS','BATS','PHLX','IEXG','CSE','NSE','FINRA','PORTAL','OTC','CME','CBOT','NYMEX','COMEX','ICE','EUREX','XETRA','CDEX','BXE','CXE','DXE','LSE','NXAM','NXBR','NXLS','NXML','NXOS','NXP') NOT NULL DEFAULT 'None',
    `altID` INT NOT NULL DEFAULT 0 COMMENT 'Alt Security ID number',
    `mic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'ISO Market Identification Code',
    `micSeg` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'ISO Market Indentification Segment Code',
    `symbol` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'stock symbol',
    `issueClass` VARCHAR(1) NOT NULL DEFAULT '' COMMENT 'issue class of stock symbol.  if no issue class field will be blank.',
    `securityID` INT NOT NULL DEFAULT 0 COMMENT 'Security ID number from the source - Vendor, SR, Feed',
    `sic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'SIC (Standard Industrial Classification) code',
    `cik` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'Central Index Key (US specific)',
    `gics` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'Global Industry Classification Standard',
    `lei` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Legal Entity Identifier',
    `naics` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'North American Industry Classification System',
    `cfi` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'ISO Classification of Financial Instruments',
    `cic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'Complementay Identification Code',
    `fisn` VARCHAR(40) NOT NULL DEFAULT '' COMMENT 'Financial Instrument Short Name',
    `isin` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'ISIN code',
    `bbgCompositeTicker` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Composite Ticker',
    `bbgExchangeTicker` VARCHAR(28) NOT NULL DEFAULT '' COMMENT 'Bloomberg Exchange Ticker',
    `bbgCompositeGlobalID` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Composite Global ID',
    `bbgGlobalID` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Global ID',
    `bbgCurrency` VARCHAR(3) NOT NULL DEFAULT '' COMMENT 'Bloomberg Trading Currency',
    `stkPriceInc` ENUM('None','FullPenny','Nickle') NOT NULL DEFAULT 'None' COMMENT 'Price increment: None; FullPenny; Nickle',
    `stkVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily stock volume',
    `futVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily future volume',
    `optVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily option volume',
    `exchString` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'exchanges listing any options on this underlying',
    `hasOptions` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'Has Options flag',
    `numOptions` INT NOT NULL DEFAULT 0 COMMENT 'total number of listed options',
    `sharesOutstanding` BIGINT NOT NULL DEFAULT 0 COMMENT 'symbol shares outstanding, represented in thousands (actualsharesoutstanding = sharesoutstanding * 1000)',
    `otcPrimaryMarket` ENUM('None','OTCLink','OTCBB','OTCLinkBB','GreyMarket','OTCBonds') NOT NULL DEFAULT 'None',
    `otcTier` ENUM('None','OtcNoTier','OTCQXUSPrem','OTCQXUS','OTCQXIntPrem','OTCQXInt','OTCQB','OTCBBOnly','PinkCurr','PinkLim','PinkNoInfo','Grey','Expert','OTCBonds') NOT NULL DEFAULT 'None',
    `otcReportingStatus` VARCHAR(1) NOT NULL DEFAULT '',
    `otcDisclosureStatus` INT NOT NULL DEFAULT 0,
    `otcFlags` INT NOT NULL DEFAULT 0,
    `tkDefSource` ENUM('None','Vendor','OTC','SR','Exchange') NOT NULL DEFAULT 'None' COMMENT 'Ticker definition source: None; Vendor; OTC; SR; Exchange',
    `statusFlag` ENUM('None','Active','Delisted') NOT NULL DEFAULT 'None',
    `timeMetric` ENUM('None','D252','D365','SRV6') NOT NULL DEFAULT 'None' COMMENT 'trading time metric - 252 or 365 trading days or a weekly cycle type',
    `tradingPeriod` ENUM('None','NMS','NMS_EXT','NMS_GTH','CME_ES','CME_ZC','CME_ZN','CME_CL','CME_GC','CFE','ICE_US','ICE_EU','EUX','EU_ERX','EU_CBOE','EU_NXAM','EU_NXBR','EU_NXLS','EU_NXML','EU_NXOS','EU_NXP') NOT NULL DEFAULT 'None',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='TickerDefinitionExt (external) records exist for all SpiderRock tickers including equity tickers (stocks and ETFs) as well as index tickers and synthetic tickers for future chains and option multihedge baskets.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `symbolType`,
    `name`,
    `issuerName`,
    `cntryOfIncorp`,
    `parValue`,
    `parValueCurrency`,
    `pointValue`,
    `pointCurrency`,
    `primaryExch`,
    `altID`,
    `mic`,
    `micSeg`,
    `symbol`,
    `issueClass`,
    `securityID`,
    `sic`,
    `cik`,
    `gics`,
    `lei`,
    `naics`,
    `cfi`,
    `cic`,
    `fisn`,
    `isin`,
    `bbgCompositeTicker`,
    `bbgExchangeTicker`,
    `bbgCompositeGlobalID`,
    `bbgGlobalID`,
    `bbgCurrency`,
    `stkPriceInc`,
    `stkVolume`,
    `futVolume`,
    `optVolume`,
    `exchString`,
    `hasOptions`,
    `numOptions`,
    `sharesOutstanding`,
    `otcPrimaryMarket`,
    `otcTier`,
    `otcReportingStatus`,
    `otcDisclosureStatus`,
    `otcFlags`,
    `tkDefSource`,
    `statusFlag`,
    `timeMetric`,
    `tradingPeriod`,
    `timestamp`
FROM `SRAnalytics`.`MsgTickerDefinitionExt`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='TickerDefinitionExt' ORDER BY ordinal_position ASC;